package DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Gui.Terminal;
import Model.Equipment;
import Model.Product;
/**
 * 
 * @author Allan Malka
 *
 */
public class DBEquipment 
{
	//If anything changes in the database, then this should be easy to find and edit:
		private final String productID = "productID";
		private final String name = "name";
		private final String productType = "ProductType";
		private final String purchasePrice = "purchasePrice";
		private final String salesPrice = "salesPrice";
		private final String countryOfOrigin = "countryOfOrigin";
		private final String stock = "stock";
		private final String minStock = "minStock";
		
		//Equipment
		private final String type = "type";
		private final String description = "description";
		//DBProduct connection
		private DBProduct dbP;
		
		//String builder
		private final String selectAll = String.format("SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s FROM Product INNER JOIN Equipment ON Product.ProductID = Equipment.EprodID", 
														productID, name, productType, purchasePrice, salesPrice, countryOfOrigin, stock, minStock, type, description);
		/*private final String createProduct = String.format("INSERT INTO Product %s, %s, %s, %s, %s, %s, %s VALUES ", 
															productID, name, purchasePrice, salesPrice, countryOfOrigin, stock, minStock);*/
		private final String createEquipment = String.format("INSERT INTO Equipment %s, %s, %s VALUES ", productID, type, description);
		private final String deleteEquipment = "DELETE FROM Product INNER JOIN Equipment ON Product.ProductID = Equipment.EProdID";
		
		private DBConnection conn;
		
		public DBEquipment()
		{
			conn = DBConnection.getInstance();
			dbP = new DBProduct();
		}
		
		public Equipment getEquipment(String _prodID)
		{
			Equipment result = null;
			
			//Create statement
			Statement stmt = null;
			try {
				stmt = conn.getDBcon().createStatement();
			}
			catch(SQLException sE) {
				Terminal.say("An error occured during statement creation. \n Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Execute statement
			String condition = WhereAdder(selectAll, productID + " = " +_prodID);
			try {
				stmt.execute(condition);
			}
			catch(SQLException sE) {
				Terminal.say("An error occured executing the statement.");
				Terminal.say(condition);
			    Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Get result
			ResultSet resSet = null;
			try {
				resSet = stmt.getResultSet();
			}
			catch(SQLException sE) {
				Terminal.say("The resultSet could not be retrieved. \n Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Build the Equipment
			if(resSet != null) {
				result = BuildEquipment(resSet);
			}
			else {
				Terminal.say("No result was found");
			}
			
			//Close connection
			if(stmt != null) {
				try {
					stmt.close();
				}
				catch(SQLException sE) {
					Terminal.say("Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
			}
			
			return result;
		}
		
		public ArrayList<Equipment> getAllEquipments()
		{
			ArrayList<Equipment> allEquipments = new ArrayList<Equipment>();
			
			//Create statement
			Statement stmt = null;
			try {
				stmt = conn.getDBcon().createStatement();
			}
			catch(SQLException sE) {
				Terminal.say("Stack Trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Execute statement
			try {
				stmt.execute(selectAll);
			}
			catch(SQLException sE) {
				Terminal.say("Stack Trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Retrieve results
			ResultSet resSet = null;
			try {
				resSet = stmt.getResultSet();
			}
			catch(SQLException sE) {
				Terminal.say("Stack Trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Build an Equipment object for each result
			try {
				do {
					allEquipments.add(BuildEquipment(resSet));
				} while(resSet.next());
			}
			catch(SQLException sE) {
				Terminal.say("Stack Trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			//Close connection
			if(stmt != null) {
				try {
					stmt.close();
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
			}
			
			return allEquipments;
		}
		
		private String WhereAdder(String queryString, String condition)
		{
			return queryString + " WHERE " + condition;
		}
		
		private Equipment BuildEquipment(ResultSet resSet)
		{
			try {
				//ResultSet starts at -1, so a first next() call is needed to find the first item.
				if(resSet.next()) {
					Product p = dbP.getProduct(productID);
					
					String prodID = p.getProductID();
					String name = p.getName();
					double pPrice = p.getPurchasePrice();
					double sPrice = p.getSalesPrice();
					String cOO = p.getCountryOfOrigin();
					String prodType = p.getProductType();
					int stock = p.getStock();
					int minStock = p.getMinStock();
					
					String type = resSet.getString(this.type);
					String description = resSet.getString(this.description);
					
					return new Equipment(prodID, name, pPrice, sPrice, cOO, prodType, stock, minStock, type, description);
				}
				else {
					return null;
				}
			}
			catch(SQLException sE) {
				Terminal.say("Could not build Equipment object \n Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
				
				return null;
			}
		}

		public int DeleteEquipment(String id)
		{
			int rc = -1;

			Statement stmt = null;
			
			try {
				stmt = conn.getDBcon().createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(WhereAdder(deleteEquipment, id));
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			return(rc);
		}

		public int CreateEquipment(Equipment equip)
		{
			int rc = -1;
			String newID = String.format("SELECT MAX(CAST(value AS Int)) FROM %s WHERE VALUE NOT LIKE '%[a-z]%' AND ISNUMERIC(VALUE) = 1", "Product");
			newID += 10;
			/*String insertPQuery = createProduct + newID + ", " + equip.getName() + ", " + equip.getPurchasePrice() + ", " 
								+ equip.getSalesPrice() + ", " + equip.getCountryOfOrigin() + ", " + equip.getStock() + ", " + equip.getMinStock();*/
			String insertEQuery = createEquipment + newID + ", " + equip.getType() + ", " + equip.getDescription();
			
			//Create statement
			Statement stmt = null;
			try {
				stmt = conn.getDBcon().createStatement();
			}
			catch(SQLException sE) {
				Terminal.say("An error occured during statement creation for insert. \n Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			
			//Execute statement
			try {
				if(dbP.InsertProduct(equip) < 0) {
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(insertEQuery);
				}
				else {
					Terminal.say("Error in insertProduct");
				}
				
			}
			catch(SQLException sE) {
				Terminal.say("An error occured executing the statement for insert.");
				Terminal.say(createEquipment );
			    Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
			
			
			//Close connection
			if(stmt != null) {
				try {
					stmt.close();
				}
				catch(SQLException sE) {
					Terminal.say("Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
			}
			return (rc);
		}
		
		public void UpdateEquipment(String _prodID, String _type, String _description, String _name, double _pPrice, double _sPrice, String _country, String _prodType, int _stock, int _minStock)
		{
			DBProduct prod = new DBProduct();
			
			Statement stmt = null;
			try {
				stmt = conn.getDBcon().createStatement();
			}
			catch(SQLException sE) {
				Terminal.say("Could not create statement: \n" + sE.getStackTrace());
			}
			String updateQuery = String.format("UPdATE Equipment SET EProdID = '%s', Type = '%s', Description = '%s' FROM Equipment", 
					_prodID, _type, _description);
			
			try {
				stmt.executeQuery(WhereAdder(updateQuery, "EProdID = " + _prodID));
				stmt.setQueryTimeout(5);
				prod.updateProduct(_prodID, _name, _pPrice, _sPrice, _country, _prodType, _stock, _minStock);
			}
			catch(SQLException sE) {
				Terminal.say("Error in executing: \n" + updateQuery);
			}

			try {
				stmt.close();
			} catch (SQLException e) {
				Terminal.say("Close Statement error", e);
			}
		}
}
